with scan_user as (
    select
         scan_user_code    as scan_user_code
        ,deliver_user_code as deliver_user_code
    from jms_dwd.dwd_tab_barscan_taking_base_dt --揽收
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,deliver_user_code
    union all
    select
         scan_user_code    as scan_user_code
        ,deliver_user_code as deliver_user_code
    from jms_dwd.dwd_tab_barscan_warehousing_base_dt --入仓
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,deliver_user_code
    union all
    select
         scan_user_code        as scan_user_code
        ,send_deliver_usercode as deliver_user_code
    from jms_dwd.dwd_tab_barscan_bagging_base_dt --建包
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_usercode
    union all
    select
         scan_user_code         as scan_user_code
        ,send_deliver_user_code as deliver_user_code
    from jms_dwd.dwd_tab_barscan_loading_base_dt --装车
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_user_code
    union all
    select
         scan_user_code         as scan_user_code
        ,send_deliver_user_code as deliver_user_code
    from jms_dwd.dwd_tab_barscan_unloading_base_dt --卸车
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_user_code
    union all
    select
         scan_user_code         as scan_user_code
        ,send_deliver_user_code as deliver_user_code
    from jms_dwd.dwd_tab_barscan_send_base_dt --发件
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_user_code
    union all
    select
         scan_user_code        as scan_user_code
        ,send_deliver_usercode as deliver_user_code
    from jms_dwd.dwd_tab_barscan_arrival_base_dt --到件
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_usercode
    union all
    select
         scan_user_code        as scan_user_code
        ,send_deliver_user_code as deliver_user_code
    from jms_dwd.dwd_tab_barscan_sitesend_base_dt --站点发件
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_user_code
    union all
    select
         scan_user_code    as scan_user_code
        ,send_user_code    as deliver_user_code
    from jms_dwd.dwd_tab_barscan_deliver_base_dt --派件
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_user_code
    union all
    select
         scan_user_code        as scan_user_code
        ,send_deliver_usercode as deliver_user_code
    from jms_dwd.dwd_tab_barscan_collect_base_dt --出库/入库
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_usercode
    union all
    select
         scan_user_code    as scan_user_code
        ,send_user_code    as deliver_user_code
    from jms_dwd.dwd_tab_barscan_sign_base_dt --签收
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_user_code
    union all
    select
         scan_user_code         as scan_user_code
        ,send_deliver_user_code as deliver_user_code
    from jms_dwd.dwd_tab_barscan_other_base_dt --其他
    where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
    group by scan_user_code
            ,send_deliver_user_code
)



insert overwrite table jms_dm.dm_staff_silence_detail_dt
select
     staff.code              as staff_code      --员工工号
    ,staff.name              as staff_name      --员工姓名
    ,dim.network_code        as network_code    --网点编码
    ,dim.network_name        as network_name    --网点名称
    ,dim.franchisee_code     as franchisee_code --加盟商编码
    ,dim.franchisee_name     as franchisee_name --加盟商名称
    ,dim.agent_code          as agent_code      --代理区编码
    ,dim.agent_name          as agent_name      --代理区名称
    ,staff.post_id           as post_id         --所属岗位ID
    ,staff.post_name         as post_name       --所属岗位名称
    ,staff.department_id     as dept_id         --部门ID
    ,staff.department_name   as dept_name       --部门名称
    ,concat(date_sub('{{ execution_date | cst_ds }}',29),'~','{{ execution_date | cst_ds }}') as stat_range      --统计时间范围
    ,staff.is_enable as is_incumbency   --是否在职
    ,case when users.staff_no  is not null then 1 else 0 end as is_enable --是否启用员工 --启用用户：剔除BEST用户
    ,case when scan.user_code  is not null then 1 else 0 end as is_scan   --是否操作扫描员工
    ,case when login.user_code is not null or scan.user_code is not null then 1 else 0 end as is_act  --是否活跃员工
    ,login.is_jms as is_jms --JMS
    ,login.is_zt  as is_zt  --智兔
    ,login.is_jc  as is_jc  --决策
    ,login.is_in  as is_in  --内场
    ,login.is_out as is_out --外场
    ,login.is_pc  as is_pc  --PC
    ,case when login.user_code is null and scan.user_code is null and users.staff_no is not null
           and to_date(users.last_login_time) < date_sub('{{ execution_date | cst_ds }}',29) then 1 else 0 end as is_silence --是否静默员工
    ,case when users.last_login_time is null or to_date(users.last_login_time) >= date_sub('{{ execution_date | cst_ds }}',29) then 1 else 0 end as is_last_login --是否最近登录
    ,users.last_login_time as last_login_time --最后一次登录时间
    ,date_add('{{ execution_date | cst_ds }}',1) as dt
from jms_dim.dim_mysql_lmdm_sys_staff staff --员工表
left join (
   select
        staff_no --员工编号
       ,last_login_time
   from jms_dim.dim_lmdm_sys_user --用户表
   where is_enable = 1 --是否启用
) users on users.staff_no = staff.code
left join (
   select
        user_code
   from (
       select
            scan_user_code as user_code --操作员
       from scan_user
       group by scan_user_code
       union all
       select
            deliver_user_code as user_code  --收派员
       from scan_user
       group by deliver_user_code
   ) a group by user_code
) scan on scan.user_code = staff.code
left join (
   select
        user_code
       ,is_jms
       ,is_zt 
       ,is_jc 
       ,is_in 
       ,is_out
       ,is_pc 
   from(
       select
           user_code as user_code
          ,max(case when app_type = 1  then 1 else 0 end) as is_jms --JMS
          ,max(case when app_type = 20 then 1 else 0 end) as is_zt  --智兔
          ,max(case when app_type = 5  then 1 else 0 end) as is_jc  --决策
          ,max(case when app_type = 3  then 1 else 0 end) as is_in  --内场
          ,max(case when app_type = 4  then 1 else 0 end) as is_out --外场
          ,max(case when app_type = 2  then 1 else 0 end) as is_pc  --PC
       from jms_dwd.dwd_sys_es_login_log_dt --登录日志表-应用类型(1:jms、2:jms-pc、3:内场app、4:外场app、5:站点app、6:jms-app)
       where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
       group by user_code
   ) a 
) login on login.user_code = staff.code--登录日志表
left join (
   select
        id
       ,code             as network_code
       ,name             as network_name
       ,franchisee_code  as franchisee_code
       ,franchisee_name  as franchisee_name
       ,case when name = '总部' then code else agent_code end as agent_code
       ,case when name = '总部' then name else agent_name end as agent_name
       ,financial_center_code
       ,financial_center_desc
   from jms_dim.dim_sys_network_detail_dt --网点维度表
   where dt = '{{ execution_date | cst_ds }}'
) dim on dim.id  = staff.network_id
left join (
    select
        staff_code
    from jms_dim.dim_sys_cross_whitelist_base --白名单
    where is_enable = 1
    and is_delete = 1
    and account_type_code = 'JCDZH' --限制交叉带白名单
) white on white.staff_code = staff.code
where staff.is_enable = 1 --限制在职员工
and nvl(dim.agent_name,0) not like '%BEST%' --剔除员工所属代理区包含BEST
and dim.id is not null --过滤测试员工
and nvl(dim.financial_center_desc,0) not like '%测试%'
and nvl(dim.financial_center_desc,0) not like '%test%'
and white.staff_code is null --剔除交叉带白名单
distribute by pmod(hash(rand()),5);
